# -*- coding: utf-8 -*-
# @Time    : 2018/4/17 22:05
# @Author  : Soft
# @File    : StuExecl.py
# @Software: PyCharm
# #Desc    : 学生记录execl导入导出
from module.ExeclUtils import getWriteBook, white, yellow, red, saveTempFile, getReadBook, deleteFile
from module.user.service import StuService


def getStuExecl(students):
    """
    传入通过page获取的学生记录数据，返回保存的记录文件
    :param students: 学生记录列表
    :return: 文件路径
    """
    book = getWriteBook()
    sheet = book.add_sheet("STUDENT")
    title = ['序号', '账号', '姓名', '电话', '班级', '注册日期', '出勤比例', '作业比例']
    for i, val in enumerate(title):  # 写入标题
        sheet.write(0, i, val.decode('utf-8'))

    for row, stu in zip(range(len(students)), students):  # 写入数据
        style = white
        if stu.get('class_num') - stu.get('record_num') == 1:
            style = yellow
        elif stu.get('class_num') - stu.get('record_num') > 1:
            style = red
        sheet.write(row + 1, 0, row + 1, style)
        sheet.write(row + 1, 1, stu.get('stu_id'), style)
        sheet.write(row + 1, 2, stu.get('stu_name'), style)
        sheet.write(row + 1, 3, stu.get('stu_tel'), style)
        sheet.write(row + 1, 4, stu.get('stu_class'), style)
        sheet.write(row + 1, 5, stu.get('stu_reg_time'), style)
        sheet.write(row + 1, 6, str(stu.get('record_num')) + "/" + str(stu.get('class_num')), style)
        sheet.write(row + 1, 7, str(stu.get('work_num')) + "/" + str(stu.get('class_num')), style)
    xls_file = saveTempFile(book, "student.xls")  # 保存临时文件
    return xls_file


def readStuExecl():
    data = getReadBook('student.xls')
    sheet = data.sheets()[0]
    rows = sheet.nrows
    for row in range(1, rows):
        arr = sheet.row_values(row)
        if not StuService.query_stu_id(arr[1]):
            StuService.add(stu_id=arr[1], stu_name=arr[2], stu_tel=arr[3], stu_class=arr[4], stu_pass=arr[1])
    deleteFile('student.xls')
